package com.carlinfo.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;


public class CRUDMain
{
	public static void main(String[] args)
	{
		System.out.println("====");
//		saveOne("唐","长安",Short.valueOf("28"),new Date());
//		selectOne(2);
//		delectOne(11);
		updateOne(9,"中国");
	}

	private static void updateOne(int i,String name)
	{
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/mydata?useSSL=false";
		String user = "root";
		String password = "123456";
		int selectOne = selectOne(i);
		if(selectOne == 0) 
		{
			return;
		}
		String sql = "update dynasty set name = ? where id = ?";
		try
		{
			Class.forName(driver);
			Connection connection = DriverManager.getConnection(url, user, password);
			PreparedStatement st = connection.prepareStatement(sql);
			st.setObject(1, name);
			st.setObject(2, i);
			st.executeUpdate();
			System.out.println("修改成功，修改数据的ID为："+selectOne);
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	private static void delectOne(int i)
	{
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/mydata?useSSL=false";
		String user = "root";
		String password = "123456";
		int selectOne = selectOne(i);
		if(selectOne == 0)
		{
			return ;
		}
		String sql = "delete from dynasty where id = ?";
		try
		{
			Class.forName(driver);
			Connection connection = DriverManager.getConnection(url, user, password);
			PreparedStatement st = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			st.setObject(1, i);
			int res = st.executeUpdate();
			System.out.println("删除成功，删除的数据ID为："+selectOne);
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	private static int selectOne(int id )
	{	
		String sql = "select * from dynasty where id = ?";
		
		String url = "jdbc:mysql://localhost:3306/mydata?useSSL=false";
		String user = "root";
		String password = "123456";
		Connection connection = null;
		PreparedStatement st = null;
		ResultSet res = null;
		try
		{
			
			String driver = "com.mysql.jdbc.Driver";
			/* 加载驱动 */
			Class.forName(driver);
			/* 获取链接 */
			connection = DriverManager.getConnection(url, user, password);
			/* 获取数据操作 */
//			Statement st = connection.createStatement();
			st = connection.prepareStatement(sql);
			st.setObject(1, id);
			/* 执行SQL语句 */
			res = st.executeQuery();
			if(res.next()) 
			{
				int id2 = res.getInt("id");
				String name = res.getString("name");
				System.out.println(id2+"\t"+name);
				return id2;
			}else 
			{
				System.out.println("没有此条数据");
				return 0;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}finally 
		{
			try
			{
				if(res != null)
				{
					res.close();
					res = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(st != null)
				{
					st.close();
					st = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(connection != null) 
				{
					connection.close();
					connection = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return 0;
	}

	private static void saveOne(String name, String capital, short age, Date st1)
	{
		String sql = "insert into dynasty " + "(`name`,capital,age,st,createTime,updateTIme) "
				+ "values (?,?,?,?	,now()	,now())";
		Connection connection = null;
		PreparedStatement st = null;
		ResultSet res = null;
		try
		{
			String drive = "com.mysql.jdbc.Driver";
			Class.forName(drive);
			String url = "jdbc:mysql://localhost:3306/mydata?useSSL=false";
			String user = "root";
			String password = "123456";
			connection = DriverManager.getConnection(url, user, password);
			st = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			st.setObject(1, name);
			st.setObject(2, capital);
			st.setObject(3, 10);
			st.setObject(4, st1);
			int rs = st.executeUpdate();
			res = st.getGeneratedKeys();
			if(res.next()) 
			{
				int id = res.getInt(1);
				System.out.println("主键："+id);
			}
			System.out.println("受影响的条数："+rs);
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally 
		{
			/* 关闭链接 */
			try
			{
				if(res != null)
				{
					res.close();
					res = null ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(st != null)
				{
					st.close();
					st = null ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(connection != null)
				{
					connection.close();
					connection = null ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
}
